import os
import pandas as pd
from tabula import read_pdf
from openpyxl import load_workbook
import fitz  # PyMuPDF
import re
import math
from tkinter import filedialog
import tkinter as tk


def extract_sample_data_from_pdf(pdf_path):
    """从PDF中提取样品数据（样品编号和Abs值）"""
    tables = read_pdf(pdf_path, pages="all", multiple_tables=True)
    combined_table = pd.concat(tables, ignore_index=True)

    # 过滤样品编号以"2"开头或包含"质控"的行
    filtered_samples = combined_table[
        (combined_table['样品编号'].astype(str).str.startswith('2')) |
        (combined_table['样品编号'].astype(str).str.contains('质控'))
        ]

    # 只保留样品编号和Abs值
    filtered_samples = filtered_samples[['样品编号', 'Abs']]
    filtered_samples.columns = [None, None]  # 清空列名

    # 添加"以下空白"行
    filtered_samples.loc[-1] = ["以下空白", ""]
    filtered_samples.index = range(len(filtered_samples))

    return filtered_samples


def extract_abs_values_from_pdf_page(pdf_path):
    """从PDF的第2页提取Abs值（包含原第一行数据）"""
    # 添加 pandas_options={'header': None} 禁止自动识别列标题
    page_data = read_pdf(pdf_path, pages=2, lattice=False, stream=True, pandas_options={'header': None})

    # 合并时保留所有行
    combined_data = pd.concat([pd.DataFrame(data) for data in page_data], ignore_index=True)

    # 提取第二列（原.iloc[:,1]）并去空值
    abs_values = combined_data.iloc[:, 1].dropna().values
    return abs_values


def extract_calibration_parameters_from_pdf(pdf_path):
    """从PDF中提取校准参数（K1, K0, 相关系数）"""
    with fitz.open(pdf_path) as doc:
        full_text = "".join(page.get_text("text") for page in doc)

    # 提取K1, K0和相关系数
    k1_match = re.search(r'K1\s*=\s*(-?\d*\.?\d+)', full_text)
    k0_match = re.search(r'K0\s*=\s*(-?\d*\.?\d+)', full_text)
    correlation_matches = re.findall(r'相关性：\s*([\d.,-]+)', full_text)

    k1 = float(k1_match.group(1)) if k1_match and k1_match.group(1) else None
    k0 = float(k0_match.group(1)) if k0_match and k0_match.group(1) else None
    correlations = [float(num.replace(',', '')) for num in correlation_matches[:3]]

    return k1, k0, correlations


def round_to_four_significant_digits(number):
    """将数值四舍五入到四位有效数字（只舍不入）"""
    if not isinstance(number, (int, float)):
        raise ValueError("输入必须为整数或浮点数。")

    if number == 0:
        return 0

    order_of_magnitude = int(math.floor(math.log10(abs(number))))
    precision = -(order_of_magnitude - 3)
    factor = 10 ** precision
    rounded_number = math.floor(number * factor) / factor

    return rounded_number


def write_data_to_excel(sample_data, abs_values, k1, k0, correlation, excel_path, sheet_names):
    """将提取的数据写入Excel文件"""
    workbook = load_workbook(excel_path)

    for sheet_name in sheet_names:
        worksheet = workbook[sheet_name]

        if sheet_name.endswith('2') or sheet_name[-2:-1] == "2":
            # 写入样品数据
            for i, row in enumerate(sample_data.itertuples(), start=5):
                worksheet.cell(row=i, column=2, value=row[1])  # 样品编号
                worksheet.cell(row=i, column=5, value=row[2])  # Abs值
        else:
            # 写入Abs值
            for j, abs_val in enumerate(abs_values, start=1):
                if j < 7:
                    worksheet.merge_cells(start_row=17, start_column=(j - 1) * 2 + 3, end_row=17,end_column=(j - 1) * 2 + 4)
                    worksheet.cell(row=17, column=(j - 1) * 2 + 3, value=abs_val)
                if len(abs_values) > 6:
                    worksheet.cell(row=17, column=15, value=abs_values[6])
                else:
                    worksheet.cell(row=17, column=15, value="/")

            # 写入K1, K0和相关系数
            worksheet.merge_cells(start_row=18, start_column=7, end_row=18, end_column=8)
            worksheet.cell(row=18, column=7).value = k1

            worksheet.merge_cells(start_row=18, start_column=11, end_row=18, end_column=12)
            worksheet.cell(row=18, column=11).value = k0

            worksheet.cell(row=18, column=15).value = correlation

    workbook.save(excel_path)


def process_pdf_file(pdf_file, excel_path, sheet_names):
    """处理单个PDF文件并写入Excel"""
    if os.path.exists(pdf_file):
        sample_data = extract_sample_data_from_pdf(pdf_file)
        abs_values = extract_abs_values_from_pdf_page(pdf_file)
        k1, k0, correlations = extract_calibration_parameters_from_pdf(pdf_file)

        # 对相关系数进行舍入
        first_correlation = float(correlations[0]) if correlations else None
        rounded_correlation = round_to_four_significant_digits(first_correlation) if first_correlation else None

        # 写入Excel
        write_data_to_excel(sample_data, abs_values, k1, k0, rounded_correlation, excel_path, sheet_names)


def main():
    """主程序：选择文件夹和Excel文件，处理所有PDF文件"""
    root = tk.Tk()
    root.withdraw()  # 隐藏主窗口

    # 选择PDF文件夹和目标Excel文件
    pdf_folder = filedialog.askdirectory(title="选择PDF文件夹")
    excel_path = filedialog.askopenfilename(title="选择目标Excel文件", filetypes=[("Excel files", "*.xlsx")])

    if not pdf_folder or not excel_path:
        print("未选择文件夹或Excel文件，程序退出。")
        return

    # PDF文件与Excel工作表的映射关系
    pdf_sheet_mapping = {
        "铬": ["铬（原吸）", "铬（原吸）2"],
        "铁": ["铁", "铁2"],
        "铜": ["铜", "铜2"],
        "锌": ["锌", "锌2"],
        "铍": ["铍", "铍2"],
        "钠": ["钠", "钠2"],
        "钾": ["钾","钾2"],
        "锰": ["锰", "锰2"],
        "锰5750": ["锰5750", "锰5750（2）"],
        "铝5750": ["铝5750", "铝5750（2）"],
        "铁5750": ["铁5750", "铁5750（2）"],
        "钡": ["钡", "钡2"],
        "钙": ["钙", "钙2"],#曲线点减少一个
        "镁": ["镁", "镁2"],#曲线点减少一个
        "铅": ["铅", "铅2"],
        "镉": ["镉", "镉2"],
        "镉（废水）": ["镉（废水）", "镉（废水）2"],
        "铅（废水）": ["铅（废水）", "铅（废水）2"],
        "镍（废水）": ["镍（废水）", "镍（废水）2"],
        "镍（地下水）": ["镍（地下水）", "镍（地下水）2"],#曲线点增加一个
    }

    # 处理每个PDF文件
    for element, sheet_names in pdf_sheet_mapping.items():
        pdf_file = os.path.join(pdf_folder, f"{element}.pdf")
        process_pdf_file(pdf_file, excel_path, sheet_names)

    print("数据已成功写入Excel文件")


if __name__ == "__main__":
    main()